import os
from typing import List, Dict, Any, Optional, Union, Callable
from contextlib import contextmanager
from functools import wraps
import pymysql
from pymysql.cursors import DictCursor
import pandas as pd
from dotenv import load_dotenv
from utils.logger import get_logger

load_dotenv()

# 获取logger实例
logger = get_logger("database")


def sql_guard(func: Callable) -> Callable:
    """SQL安全装饰器，只允许执行SELECT/SHOW/DESCRIBE语句"""
    @wraps(func)
    def wrapper(*args, **kwargs):
        # 找到SQL参数（类方法第二个参数，普通函数第一个参数）
        sql_arg_index = 1 if args and hasattr(args[0], '__dict__') else 0  # 检查是否是类方法
        
        if len(args) > sql_arg_index and isinstance(args[sql_arg_index], str):
            sql = args[sql_arg_index].strip().upper()
            if sql.split(' ')[0] not in ['SELECT', 'SHOW', 'DESCRIBE']:
                logger.error(f"🚫 Not permitted: Only SELECT/SHOW/DESCRIBE statements are allowed: {sql}")
                raise ValueError("Not permitted: Only SELECT/SHOW/DESCRIBE statements are allowed")
        return func(*args, **kwargs)
    return wrapper


class DatabaseClient:
    """简化的数据库客户端，主要用于执行只读SQL查询"""
    
    def __init__(self):
        self.connection_config = self._get_connection_config()
        logger.info(f"🔧 数据库配置加载完成: {self.connection_config['host']}:{self.connection_config['port']}/{self.connection_config['database']}")
    
    def _get_connection_config(self) -> Dict[str, Any]:
        """获取数据库连接配置"""
        return {
            'host': os.getenv("DB_HOST"),
            'port': int(os.getenv("DB_PORT", "3306")),
            'user': os.getenv("DB_USER", "agi"),
            'password': os.getenv("DB_PASSWORD"),
            'database': os.getenv("DB_NAME"),
            'charset': 'utf8mb4',
            'autocommit': True,
            'cursorclass': DictCursor,  # 使用字典游标，方便处理结果
            'connect_timeout': 10,
            'read_timeout': 30,
            'write_timeout': 30
        }
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        connection = None
        try:
            connection = pymysql.connect(**self.connection_config)
            logger.debug("✅ 数据库连接建立成功")
            yield connection
        except Exception as e:
            logger.error(f"❌ 数据库连接失败: {e}")
            raise
        finally:
            if connection:
                connection.close()
                logger.debug("🔒 数据库连接已关闭")
    
    @sql_guard
    def execute_query(self, sql: str, params: Optional[Union[tuple, dict]] = None) -> List[Dict[str, Any]]:
        """
        执行SQL查询并返回结果
        
        Args:
            sql: SQL查询语句，只允许SELECT/SHOW/DESCRIBE语句
            params: 查询参数，支持元组或字典格式
            
        Returns:
            查询结果列表，每个元素为字典格式的一行数据
        """
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    logger.debug(f"🔍 执行SQL: {sql}")
                    if params:
                        logger.debug(f"📝 参数: {params}")
                    
                    cursor.execute(sql, params)
                    results = cursor.fetchall()
                    
                    logger.info(f"✅ 查询完成，返回 {len(results)} 条记录")
                    return results
                    
        except Exception as e:
            logger.error(f"❌ SQL查询执行失败: {e}")
            logger.error(f"📄 SQL: {sql}")
            logger.error(f"📝 参数: {params}")
            raise
    
    @sql_guard
    def execute_query_to_dataframe(self, sql: str, params: Optional[Union[tuple, dict]] = None) -> pd.DataFrame:
        """
        执行SQL查询并返回Pandas DataFrame
        
        Args:
            sql: SQL查询语句
            params: 查询参数
            
        Returns:
            包含查询结果的DataFrame
        """
        try:
            results = self.execute_query(sql, params)
            df = pd.DataFrame(results)
            logger.info(f"📊 查询完成，返回 DataFrame shape: {df.shape}")
            return df
        except Exception as e:
            logger.error(f"❌ 查询转DataFrame失败: {e}")
            raise
    
    def get_table_info(self, table_name: str) -> List[Dict[str, Any]]:
        """
        获取表结构信息
        
        Args:
            table_name: 表名
            
        Returns:
            表结构信息
        """
        sql = "DESCRIBE `{}`".format(table_name)
        return self.execute_query(sql)
    
    def get_all_tables(self) -> List[str]:
        """获取数据库中所有表名"""
        sql = "SHOW TABLES"
        results = self.execute_query(sql)
        # 提取表名（字典的值）
        table_key = f"Tables_in_{self.connection_config['database']}"
        return [row[table_key] for row in results]
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            result = self.execute_query("SELECT 1 as test")
            return len(result) == 1 and result[0]['test'] == 1
        except Exception as e:
            logger.error(f"❌ 数据库连接测试失败: {e}")
            return False


class WorkHoursQueryHelper:
    """工时查询辅助类，提供常用的业务查询方法"""
    GET_PEOPLE_WORKLOGS_SQL = """
    SELECT l.LogText,l.WorkHour,l.WorkHourType,l.LogTime, p.ProjectName, p.ProjectId 
        FROM log l
        LEFT JOIN ProjectInf p ON l.ProjectDataId = p.Id
        WHERE l.PeopleName = %s 
        AND DATE(l.LogTime) BETWEEN %s AND %s
        ORDER BY l.LogTime DESC
    """
    GET_FRAMEWORK_WORKLOGS_SQL = """
        SELECT l.LogText,
            l.PeopleName,
            l.WorkHour,l.WorkHourType,
            l.LogTime, 
            p.ProjectName, p.ProjectId, pi.PersonnelLevel,f.FrameworkName
        FROM log l
        LEFT JOIN ProjectInf p ON l.ProjectDataId = p.Id
        LEFT JOIN Peopleinf pi ON l.PeopleName = pi.PeopleName
        LEFT JOIN framework f ON pi.FrameworkId = f.Id
        WHERE f.FrameworkName = %s 
        AND DATE(l.LogTime) BETWEEN %s AND %s
        ORDER BY l.LogTime DESC
    """
    GET_PROJECT_WORKLOGS_SQL = """
    SELECT l.LogText,l.WorkHour,l.WorkHourType,l.LogTime, p.ProjectName, p.ProjectId , p.ProjectName, p.ProjectId
        FROM log l
        LEFT JOIN ProjectInf p ON l.ProjectDataId = p.Id
        WHERE p.ProjectId = %s 
        AND DATE(l.LogTime) BETWEEN %s AND %s
        ORDER BY l.LogTime DESC
    """
    GET_COMPREHENSIVE_REPORT_SQL = """
        SELECT 
            l.LogText,
            l.PeopleName,
            l.WorkHour,
            l.WorkHourType,
            l.LogTime,
            p.ProjectName,
            p.ProjectId,
            f.FrameworkName
        FROM log l
        LEFT JOIN ProjectInf p ON l.ProjectDataId = p.Id
        LEFT JOIN Peopleinf pi ON l.PeopleName = pi.PeopleName
        LEFT JOIN framework f ON pi.FrameworkId = f.Id
        WHERE 1=1
    """
    GET_PEOPLE_TOTAL_HOURS_SQL = """
    SELECT COALESCE(SUM(WorkHour), 0) as total_hours
        FROM log 
        WHERE PeopleName = %s 
        AND DATE(LogTime) BETWEEN %s AND %s
    """
    GET_ALL_PEOPLE_SQL = """
    SELECT 
        pi.Id as id,
        pi.PeopleName,
        pi.FrameworkId,
        pi.PersonnelLevel,
        f.FrameworkName
    FROM Peopleinf pi
    LEFT JOIN framework f ON pi.FrameworkId = f.Id
    ORDER BY pi.PeopleName
    """
    GET_ALL_FRAMEWORKS_SQL = """
    SELECT 
        Id as id,
        FrameworkName
    FROM framework 
    ORDER BY FrameworkName
    """
    GET_ALL_PROJECTS_SQL = """
    SELECT 
        Id,
        ProjectId,
        ProjectName,
        ProjectType,
        ProjectState as Project,
        CreateTime
    FROM ProjectInf 
    ORDER BY ProjectName
    """

    def __init__(self, db_client: DatabaseClient):
        self.db = db_client
    
    def get_people_worklogs(self, people_name: str, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        """
        查询指定员工的工时日志
        
        Args:
            people_name: 员工姓名
            start_date: 开始日期 (YYYY-MM-DD)
            end_date: 结束日期 (YYYY-MM-DD)
        """
        return self.db.execute_query(self.GET_PEOPLE_WORKLOGS_SQL, (people_name, start_date, end_date))
    
    def get_framework_worklogs(self, framework_name: str, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        """
        查询指定部门的工时日志
        
        Args:
            framework_name: 部门名称
            start_date: 开始日期 (YYYY-MM-DD)
            end_date: 结束日期 (YYYY-MM-DD)
        """
        return self.db.execute_query(self.GET_FRAMEWORK_WORKLOGS_SQL, (framework_name, start_date, end_date))
    
    def get_project_worklogs(self, project_id: str, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        """
        查询指定项目的工时日志
        
        Args:
            project_id: 项目编号
            start_date: 开始日期 (YYYY-MM-DD)
            end_date: 结束日期 (YYYY-MM-DD)
        """
        return self.db.execute_query(self.GET_PROJECT_WORKLOGS_SQL, (project_id, start_date, end_date))
    
    def get_comprehensive_report(self, people_name: str = None, framework_name: str = None, 
                                project_id: str = None, start_date: str = None, end_date: str = None) -> List[Dict[str, Any]]:
        """
        综合查询工时日志，支持多条件组合
        
        Args:
            people_name: 员工姓名（可选）
            framework_name: 部门名称（可选）
            project_id: 项目编号（可选）
            start_date: 开始日期（可选）
            end_date: 结束日期（可选）
        """
        # 构建动态SQL (使用局部变量，不修改类属性)
        sql = self.GET_COMPREHENSIVE_REPORT_SQL
        params = []
        
        if people_name:
            sql += " AND l.PeopleName = %s" 
            params.append(people_name)
        
        if framework_name:
            sql += " AND f.FrameworkName = %s"
            params.append(framework_name)
            
        if project_id:
            sql += " AND p.ProjectId = %s"
            params.append(project_id)
            
        if start_date:
            sql += " AND DATE(l.LogTime) >= %s"
            params.append(start_date)
            
        if end_date:
            sql += " AND DATE(l.LogTime) <= %s"
            params.append(end_date)
        
        sql += " ORDER BY l.LogTime DESC"
        
        return self.db.execute_query(sql, tuple(params) if params else None)
    
    def get_people_total_hours(self, people_name: str, start_date: str, end_date: str) -> float:
        """计算员工在指定时间范围内的总工时"""
        result = self.db.execute_query(self.GET_PEOPLE_TOTAL_HOURS_SQL, (people_name, start_date, end_date))
        return float(result[0]['total_hours']) if result else 0.0
    
    def get_all_people(self) -> List[Dict[str, Any]]:
        """获取所有员工信息"""
        return self.db.execute_query(self.GET_ALL_PEOPLE_SQL)
    
    def get_all_frameworks(self) -> List[Dict[str, Any]]:
        """获取所有部门信息"""
        return self.db.execute_query(self.GET_ALL_FRAMEWORKS_SQL)
    
    def get_all_projects(self) -> List[Dict[str, Any]]:
        """获取所有项目信息"""
        return self.db.execute_query(self.GET_ALL_PROJECTS_SQL)


# 全局实例
db_client = DatabaseClient()
query_helper = WorkHoursQueryHelper(db_client)

# 导出函数
def get_db_client() -> DatabaseClient:
    """获取数据库客户端实例"""
    return db_client

def get_query_helper() -> WorkHoursQueryHelper:
    """获取查询辅助类实例"""
    return query_helper

@sql_guard
def execute_sql(sql: str, params: Optional[Union[tuple, dict]] = None) -> List[Dict[str, Any]]:
    """直接执行SQL查询的便捷函数"""
    return db_client.execute_query(sql, params)

@sql_guard
def execute_sql_to_df(sql: str, params: Optional[Union[tuple, dict]] = None) -> pd.DataFrame:
    """执行SQL查询并返回DataFrame的便捷函数"""
    return db_client.execute_query_to_dataframe(sql, params) 